package com.pineapple.module.core.standards.pineapple.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pineapple.module.core.standards.pineapple.domain.Pineapple;
import com.pineapple.module.core.standards.pineapple.domain.PineappleCriteria;
import com.pineapple.module.core.standards.pineapple.domain.PineappleSearch;

public class PineappleDAO {

	protected List<PineappleSearch> search(PineappleCriteria criteria,Connection conn) throws Exception{
		List<PineappleSearch> lstPineapple = new ArrayList<PineappleSearch>();
		PineappleSearch pineapple = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM pineapple ");
		sql.append("WHERE 1=1 ");

		if(criteria.getPineappleId()!=null && !criteria.getPineappleId().equals("")){
			sql.append("AND pineapple_id  LIKE '"+criteria.getPineappleId()+"%' ");
		}
		if(criteria.getPineappleName()!=null && !criteria.getPineappleName().equals("")){
			sql.append("AND pineapple_name LIKE '"+criteria.getPineappleName()+"%' ");
		}


		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	pineapple= new PineappleSearch();
		        	pineapple.setPineappleId(rs.getString("pineapple_id"));
		        	pineapple.setPineappleName(rs.getString("pineapple_name"));
		        	pineapple.setPineappleCultivation(rs.getString("pineapple_cultivation"));

		        	lstPineapple.add(pineapple);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstPineapple;
	}



	protected void add(Connection conn, Pineapple pineapple)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO pineapple (pineapple_name,pineapple_cultivation)");
		sql.append("VALUES (");

		sql.append("'"+pineapple.getPineappleName()+"' ");
		sql.append(",'"+pineapple.getPineappleCultivation()+"' ");
		sql.append(" )");

			System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}
	protected void edit(Connection conn, Pineapple pineapple)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE pineapple SET ");
		sql.append("pineapple_name='"+pineapple.getPineappleName()+"' ");
		sql.append(",pineapple_cultivation='"+pineapple.getPineappleCultivation()+"' ");
		sql.append(" WHERE pineapple_id="+pineapple.getPineappleId());


		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String pineappleIds)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM pineapple  ");

		sql.append(" WHERE pineapple_id IN ("+pineappleIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected Pineapple searchById(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM pineapple ");
		sql.append("WHERE pineapple_id ="+id);
		Pineapple pineapple = null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			   pineapple = new Pineapple();
		        if(rs.next()) {
		        	pineapple.setPineappleId(rs.getString("pineapple_id"));
		        	pineapple.setPineappleName(rs.getString("pineapple_name"));
		        	pineapple.setPineappleCultivation(rs.getString("pineapple_cultivation"));

		        }

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return pineapple;
	}
}
